package com.easy.common.util.string;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.easy.common.exception.ServiceException;
import com.easy.modules.sysmgr.web.form.UserForm;

public class ExcelUtils {

	public static Workbook getWorkbook(String path) throws IOException {
		File file = new File(path);

		Workbook wb = null;
		try {
			wb = new XSSFWorkbook(new FileInputStream(file));
		} catch (Exception ex) {
			wb = new HSSFWorkbook(new FileInputStream(file));
		}

		return wb;
	}
	
	/**
	 * 方法描述 : 获取指定的sheet页及指定的开始行结束行和指定列 
	 * 创建时间： 2014-5-25 下午3:18:16
	 * 创建者：杨浩泉 
	 * 版本： v1.0
	 * @param path			Excel文件绝对路径
	 * @param sheetName		sheet名称
	 * @param startRowIndex	指定读取的开始行的索引，没有指定则从0开始
	 * @param endRowIndex	指定读取的结束行的索引，没有指定则读取所有行
	 * @param callIndex		指定读取列的索引，没有指定则读取所有列
	 * @return
	 * @throws IOException 
	 */
	public static ArrayList<ArrayList<Object>> getSheetRows(String path, Object sheetName, int startRowIndex, Integer endRowIndex, Integer[] callIndex) throws IOException{
		ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
		
		Workbook workbook = getWorkbook(path) ;
		Sheet sheet = null ;
		if(sheetName instanceof String) {
			sheet = workbook.getSheet(sheetName.toString()) ;
		} else if(sheetName instanceof Integer) {
			sheet = workbook.getSheetAt((Integer) sheetName) ;
		}
		if(null == sheet) {
			throw new ServiceException("没有找到指定的Sheet页！") ;
		}
		
		int lastRowNum = (endRowIndex==null||endRowIndex<0?sheet.getLastRowNum():endRowIndex) ;
		for(int i=startRowIndex; i<lastRowNum; i++) {
			Row row = sheet.getRow(i);
			if (row == null) {
				throw new ServiceException("没有找到指定的行数!") ;
			}
			
			ArrayList<Object> cellList = new ArrayList<Object>();
			if(null != callIndex && callIndex.length > 0) {//指定读取的列		
				for(int j=0; j<callIndex.length; j++) {
					cellList.add(readCell(row.getCell(callIndex[j])));
				}
			} else {//读取所有列											
				for (Cell cell : row) {
					cellList.add(readCell(cell));
				}
			}
			
			rowList.add(cellList) ;
		}
		return rowList;
	}
	

	private static Object readCell(Cell cell) {
		if (cell == null) {
			return null;
		}
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			String str = cell.getRichStringCellValue().getString();
			return str == null ? "" : str.trim();
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			} else {
				return cell.getNumericCellValue();
			}
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue();
		case Cell.CELL_TYPE_FORMULA:
			if (DateUtil.isCellDateFormatted(cell)) {
				return cell.getDateCellValue();
			} else {
				return cell.getCellFormula();
			}
		case Cell.CELL_TYPE_BLANK:
			return "";
		default:
			System.err.println("Data error for cell of excel: " + cell.getCellType());
			return "";
		}

	}
	
	public static void main(String[] args) {
		String path = "D:/work/workspaces/Project03/easy/backup/员工信息记录.xls" ;
		try {
			
			ArrayList<ArrayList<Object>> sheetRows = ExcelUtils.getSheetRows(path, "sheet2", 1, null, new Integer[]{0, 1, 2, 3, 5, 6}) ;
			
			for (ArrayList<Object> arrayList : sheetRows) {
				UserForm u = new UserForm() ;
				u.setNum(arrayList.get(0).toString()) ;
				u.setAccount(arrayList.get(2).toString()) ;
				u.setTruename(arrayList.get(1).toString()) ;
				u.setSex(arrayList.get(3).toString()) ;
				u.setMobile(arrayList.get(4).toString()) ;
				u.setEmail(arrayList.get(5).toString()) ;

				System.out.println(u);
				
			}
			
		} catch (Exception e) {
			e.printStackTrace() ;
		}
	}
	

}
